/*
 * Copyright 2002-2016 the atnoce.com.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.atnoce.onepass.dao;

import com.atnoce.onepass.pojo.PasswordItem;
import com.atnoce.onepass.pojo.PasswordItemSync;
import com.atnoce.onepass.utils.Cache;
import com.atnoce.onepass.utils.DbUtils;
import com.atnoce.onepass.utils.QueueType;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author atnoce.com
 * @date 2016/11/4
 * @since 1.0.0
 */
public class PasswordItemDao {
    private Statement statement=null;
    private void initStatement() throws SQLException, ClassNotFoundException{
        if(statement==null){
            statement= DbUtils.getDBConnection().createStatement();
        }
    }
    /**
     * 通过分类名称获取密码条目
     * @param dirName
     * @return
     * @throws SQLException
     */
    public ObservableList<PasswordItem> getPasswordsFormDirName(String dirName) throws SQLException, ClassNotFoundException{
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
        ps.setString(1, dirName);
        ResultSet executeQuery = ps.executeQuery();
        String dirId = null;
        if(executeQuery.next()){
            dirId=executeQuery.getString("dirId");
        }
        executeQuery.close();
        ps.close();
        if(dirId!=null){
            ps=DbUtils.getDBConnection().prepareStatement("select * from passitem where dirTypeId=? and isDelete='false';");
            ps.setString(1, dirId);

            ResultSet query = ps.executeQuery();
            ObservableList<PasswordItem> oblist= FXCollections.observableArrayList();
            while(query.next()){
                String id=query.getString("passId");
                String account=query.getString("account");
                String pass=query.getString("passwordStr");
                String remarkStr=query.getString("remarkStr");
                String createTime=query.getString("createTime");
                String isDelete=query.getString("isDelete");
                String dirTypeId=query.getString("dirTypeId");
                String sync=query.getString("sync");
                String modifyTime=query.getString("modifyTime");
                oblist.add(new PasswordItem(id,account, pass, remarkStr, dirTypeId,createTime,modifyTime, isDelete,sync));
            }

            return oblist;
        }
        return null;
    }
    /**
     * 添加密码条目
     * @param passwordItem
     * @param dirName
     * @return
     * @throws SQLException
     */
    public boolean addPasswordItem(PasswordItem passwordItem,String dirName) throws SQLException, ClassNotFoundException{
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
        ps.setString(1, dirName);
        ResultSet rs = ps.executeQuery();
        String dirId = null;
        if(rs.next()){
            dirId=rs.getString("dirId");
        }
        ps.close();
        if(dirId!=null){
            ps=DbUtils.getDBConnection().prepareStatement("insert into passitem values (?,?,?,?,?,?,?,?,?);");
            ps.setString(1, passwordItem.getPassId());
            ps.setString(2, passwordItem.getAccount());
            ps.setString(3, passwordItem.getPasswordStr());
            ps.setString(4, passwordItem.getRemarkStr());
            ps.setString(5, passwordItem.getCreateTime());
            ps.setString(6, passwordItem.getModifyTime());
            ps.setString(7, passwordItem.getIsDelete());
            ps.setString(8, dirId);
            ps.setString(9, passwordItem.getSync());
            ps.execute();

            int updateCount = ps.getUpdateCount();
            if(updateCount==1){
                return true;
            }
            return false;
        }
        return false;
    }
    public boolean addPasswordItem(PasswordItem passwordItem) throws SQLException, ClassNotFoundException {
        PreparedStatement ps = DbUtils.getDBConnection().prepareStatement("insert into passitem values (?,?,?,?,?,?,?,?,?);");
        ps.setString(1, passwordItem.getPassId());
        ps.setString(2, passwordItem.getAccount());
        ps.setString(3, passwordItem.getPasswordStr());
        ps.setString(4, passwordItem.getRemarkStr());
        ps.setString(5, passwordItem.getCreateTime());
        ps.setString(6, passwordItem.getModifyTime());
        ps.setString(7, passwordItem.getIsDelete());
        ps.setString(8, passwordItem.getDirTypeId());
        ps.setString(9, passwordItem.getSync());
        ps.execute();

        int updateCount = ps.getUpdateCount();
        if(updateCount==1){
            return true;
        }
        return false;
    }
    /**
     * 删除密码条目
     * @param passwordItem
     * @return
     * @throws SQLException
     */
    public boolean deletePasswordItem(PasswordItem passwordItem) throws SQLException, ClassNotFoundException{
        PreparedStatement preparedStatement=DbUtils.getDBConnection().prepareStatement("update passitem set isDelete='true' where passId=?;");
        preparedStatement.setString(1, passwordItem.getPassId());
        preparedStatement.execute();
        int updateCount = preparedStatement.getUpdateCount();
        if(updateCount==1){
            Map<QueueType,String> queue=new HashMap<>();
            queue.put(QueueType.PASSITEM,passwordItem.getPassId());
            Cache.addUpdateModifyTimeQueue(queue);
            return true;
        }
        return false;
    }
    /**
     * 通过分类ID删除密码项
     * @param dirName
     * @throws SQLException
     */
    public void deletePasswordItemFromDirType(String dirName) throws SQLException, ClassNotFoundException{
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
        ps.setString(1, dirName);
        ResultSet executeQuery = ps.executeQuery();
        String dirId = null;
        if(executeQuery.next()){
            dirId=executeQuery.getString("dirId");
        }
        ps.close();
        if(dirId!=null){
            ps=DbUtils.getDBConnection().prepareStatement("update passitem set isDelete='true' where dirTypeId=?;");
            ps.setString(1, dirId);
            ps.execute();

            ps=DbUtils.getDBConnection().prepareStatement("SELECT * FROM passitem WHERE dirTypeId=?");
            ps.setString(1,dirId);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.next()){
                Map<QueueType,String> queue=new HashMap<>();
                queue.put(QueueType.PASSITEM,resultSet.getString("passId"));
                Cache.addUpdateModifyTimeQueue(queue);
            }
            ps.close();
        }

    }
    /**
     * 更新密码项
     * @param passwordItem
     * @param dirName
     * @return
     * @throws SQLException
     */
    public boolean updatePasswordItem(PasswordItem passwordItem,String dirName) throws SQLException, ClassNotFoundException{
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from fenlei where dirName=?;");
        ps.setString(1, dirName);
        ResultSet executeQuery = ps.executeQuery();
        String dirId = null;
        if(executeQuery.next()){
            dirId=executeQuery.getString("dirId");
        }
        ps.close();
        if(dirId!=null){
            ps=DbUtils.getDBConnection().prepareStatement("update passitem set account=?,passwordStr=?,remarkStr=?," +
                    "dirTypeId=?,sync='false',modifyTime=?,isDelete=? where passId=?;");
            ps.setString(1, passwordItem.getAccount());
            ps.setString(2, passwordItem.getPasswordStr());
            ps.setString(3, passwordItem.getRemarkStr());
            ps.setString(4, passwordItem.getDirTypeId());
            ps.setString(5, passwordItem.getModifyTime());
            ps.setString(6,passwordItem.getIsDelete());
            ps.setString(7, passwordItem.getPassId());

            ps.execute();
            int updateCount = ps.getUpdateCount();
            ps.close();
            if(updateCount==1){
                return true;
            }
            return false;
        }
        return false;

    }
    /**
     * 通过密码项的ID获取密码项对象
     * @param passId
     * @return
     * @throws java.sql.SQLException
     */
    public PasswordItem getPasswordItemFromPassId(String passId) throws SQLException, ClassNotFoundException{
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from passitem where passId=?;");
        ps.setString(1, passId);
        ResultSet rs = ps.executeQuery();
        if(rs.next()){
            String id=rs.getString("passId");
            String account=rs.getString("account");
            String pass=rs.getString("passwordStr");
            String remarkStr=rs.getString("remarkStr");
            String createTime=rs.getString("createTime");
            String isDelete=rs.getString("isDelete");
            String dirTypeId=rs.getString("dirTypeId");
            String sync=rs.getString("sync");
            String modifyTime=rs.getString("modifyTime");
            return new PasswordItem(id,account, pass, remarkStr, dirTypeId,createTime,modifyTime, isDelete,sync);
        }
        return null;
    }
    /**
     * 获取所有密码项
     * @return
     * @throws SQLException
     */
    public List<PasswordItem> getAllPasswordItem() throws SQLException, ClassNotFoundException{
        List<PasswordItem> list=new ArrayList<>();
        initStatement();
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("select * from passitem where isDelete='false'");
        ResultSet rs= ps.executeQuery();
        //ResultSet executeQuery = statement.executeQuery("select * from passitem");
        PasswordItem pi;
        while(rs.next()){
            pi=new PasswordItem();
            pi.setPassId(rs.getString("passId"));
            pi.setAccount(rs.getString("account"));
            pi.setPasswordStr(rs.getString("passwordStr"));
            pi.setRemarkStr(rs.getString("remarkStr"));
            pi.setCreateTime(rs.getString("createTime"));
            pi.setModifyTime(rs.getString("modifyTime"));
            pi.setIsDelete(rs.getString("isDelete"));
            pi.setDirTypeId(rs.getString("dirTypeId"));
            pi.setSync(rs.getString("sync"));

            list.add(pi);
        }
        return list;
    }

    /**
     * 同步时调用该方法获取所有需要同步的密码项<br>
     * 查询语句：SELECT * FROM passitem where modifyTime is not NULL
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<PasswordItemSync> getSyncPasswordItem() throws SQLException, ClassNotFoundException {
        PreparedStatement ps = DbUtils.getDBConnection().prepareStatement("SELECT * FROM passitem where modifyTime is not NULL;");
        ResultSet rs = ps.executeQuery();
        List<PasswordItemSync> list=new ArrayList<>();
        PasswordItemSync pi;
        while (rs.next()){
            pi=new PasswordItemSync();
            pi.setPassId(rs.getString("passId"));
            pi.setAccount(rs.getString("account"));
            pi.setPasswordStr(rs.getString("passwordStr"));
            pi.setRemarkStr(rs.getString("remarkStr"));
            pi.setCreateTime(rs.getString("createTime"));
            pi.setModifyTime(rs.getString("modifyTime"));
            pi.setIsDelete(rs.getString("isDelete"));
            pi.setDirTypeId(rs.getString("dirTypeId"));
            pi.setSync(rs.getString("sync"));

            list.add(pi);
        }
        return list;
    }

    /**
     * 通过密码项ID获取密码条目
     * @param passwordItem
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public boolean updatePasswordItemFromId(PasswordItem passwordItem) throws SQLException, ClassNotFoundException{
        PreparedStatement ps=DbUtils.getDBConnection().prepareStatement("update passitem set account=?,passwordStr=?," +
                "remarkStr=?,dirTypeId=?,modifyTime=?,sync=?,isDelete=? where passId=?;");
        ps.setString(1, passwordItem.getAccount());
        ps.setString(2, passwordItem.getPasswordStr());
        ps.setString(3, passwordItem.getRemarkStr());
        ps.setString(4, passwordItem.getDirTypeId());
        ps.setString(5, passwordItem.getModifyTime());
        ps.setString(6, passwordItem.getSync());
        ps.setString(7,passwordItem.getIsDelete());
        ps.setString(8,passwordItem.getPassId());
        int executeUpdate = ps.executeUpdate();
        if(executeUpdate==1){
            return true;
        }
        return false;
    }

    public void updateModifyTime(String id,String time) throws SQLException, ClassNotFoundException {
        PasswordItem passwordItem = getPasswordItemFromPassId(id);
        passwordItem.setModifyTime(time);
        updatePasswordItemFromId(passwordItem);
    }
}
